package com.fanhang.samples.spring.sql2o.common;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.support.JdbcAccessor;
import org.springframework.util.Assert;
import org.sql2o.Query;
import org.sql2o.logging.LocalLoggerFactory;
import org.sql2o.logging.Logger;

public class Sql2oSessionTemplate extends JdbcAccessor implements Sql2oOperations {
	private final static Logger logger = LocalLoggerFactory.getLogger(Sql2oSessionTemplate.class);
	
	private Query createQuery(CharSequence sql) {
		Sql2oSession session = new DefaultSql2oSession(this.getDataSource());
		return session.createQuery(sql.toString());
	}
	
	private Query createQuery(CharSequence sql, Map<String, ?> paramMap) {
		return addParameter(createQuery(sql.toString()), paramMap);
	}
	
	private Query addParameter(Query query, Map<String, ?> paramMap) {
		if (paramMap != null && !paramMap.isEmpty()) {
			Map<String, List<Integer>> paramNames = query.getParamNameToIdxMap();
			for (String key : paramNames.keySet()) {
				query.addParameter(key, paramMap.get(key));
			}
		}
		return query;
	}

	public int update(CharSequence sql) {
		return this.update(sql, null);
	}
	
	/**
	 * 
	 * @param sql
	 * @param bean
	 * @return
	 * @see NamedParameterJdbcDaoSupport
	 */
	public int update(CharSequence sql, Map<String, ?> paramMap) {
		Assert.notNull(sql);
		Assert.notNull(paramMap);
		logger.debug("update: {}", sql);
		return createQuery(sql, paramMap).executeUpdate().getResult();
	}
	
	public int[] update(String[] sqls) throws DataAccessException {
		int[] updatedRows = new int[sqls.length];
		for (int i = 0; i < sqls.length; i++) {
			updatedRows[i] = update(sqls[i]);
		}
		return updatedRows;
	}

	public int updates(String sql, List<Map<String, ?>> paramsList) throws DataAccessException {
		Query query = createQuery(sql);
		for (Map<String, ?> paramMap : paramsList) {
			addParameter(query, paramMap);
			query.addToBatch();
		}
		return query.executeBatch().getResult();
	}
	
	/**
	 * 根据SQL查询数据库，获得一个查询条数
	 * 
	 * @param sql
	 *            SQL查询语句
	 * @return List(String)
	 * @throws DataAccessException
	 */
	public Integer getCount(CharSequence sql) throws DataAccessException {
		return this.getCount(sql, null);
	}
	
	/**
	 * 根据SQL查询查询数据条数
	 * 
	 * @param sql
	 *            SQL查询语句
	 * @return 
	 * @throws DataAccessException
	 */
	public Integer getCount(CharSequence sql, Map<String, ?> paramMap) throws DataAccessException {
		Assert.notNull(sql);
		
		return createQuery(sql, paramMap).executeAndFetchFirst(Integer.class);
	}
	
	/**
	 * 根据SQL语句查询数据库，并从结果集的起始列至终止列获得元素为String[]的列表。
	 * 
	 * @param sql
	 *            SQL查询语句
	 * @return List(String[])元素为String[]的列表
	 * @throws DataAccessException
	 */
	public <T> T fetchFirst(CharSequence sql, Class<T> clazz) throws DataAccessException {
		return this.fetchFirst(sql, null, clazz);
	}
	
	/**
	 * 根据SQL语句查询数据库，并从结果集的起始列至终止列获得元素为String[]的列表。
	 * 
	 * @param sql
	 *            SQL查询语句
	 * @return List(String[])元素为String[]的列表
	 * @throws DataAccessException
	 */
	public <T> T fetchFirst(CharSequence sql, Map<String, ?> paramMap, Class<T> clazz) throws DataAccessException {
		Assert.notNull(sql);
		Assert.notNull(clazz);
		
		return createQuery(sql, paramMap).executeAndFetchFirst(clazz);
	}
	
	/**
	 * @throws DataAccessException
	 */
	public <T> List<T> fetch(CharSequence sql, Class<T> clazz) throws DataAccessException {
		return this.fetch(sql, new HashMap<String, Object>(), clazz);
	}
	
	/**
	 * @throws DataAccessException
	 */
	public <T> List<T> fetch(CharSequence sql, Map<String, ?> paramMap, Class<T> clazz) throws DataAccessException {
		Assert.notNull(sql);
		Assert.notNull(clazz);
		
		return createQuery(sql, paramMap).executeAndFetch(clazz);
	}

}
